from sqlalchemy.exc import PendingRollbackError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String, Float, BigInteger, DateTime, text
from inits import dbSession

# 定义基类
Base = declarative_base()

class his_kpi(Base):
    __tablename__ = 'his_kpi'  # 数据库中表的名字
    id = Column(Integer, primary_key=True)
    staff_name = Column(String(255))
    staff_id = Column(String(20))
    labor_amount = Column(Integer)
    labor_user_amount = Column(Integer)
    alarm_conversation_amount = Column(Integer)
    sales_amount = Column(String(255))
    inquirer_amount = Column(String(255))
    purchasers_amount = Column(String(255))
    satisfaction_rate = Column(String(10))
    fcr_rate = Column(String(255))
    satisfaction_rate_compare = Column(String(255))
    inquiry_conversion_rate = Column(String(255))
    online_duration = Column(String(255))
    nap_duration = Column(String(255))
    offline_duration = Column(String(255))
    avg_response_duration = Column(String(255))
    thirty_sec_response_rate = Column(String(255))
    three_min_response_rate = Column(String(255))
    three_min_response_rate_compare = Column(String(255))
    three_min_response_con_rate = Column(String(10))
    staff_username = Column(String(255))
    comment_type1_map = Column(String(255))
    labor_amount8_to23 = Column(String(255))
    cpd_online_duration = Column(String(255))
    cpd_map = Column(String(255))
    first_response_duration = Column(String(255))
    dissatisfaction_rate = Column(String(255))
    create_order_amount = Column(String(255))
    insert_time = Column(DateTime)
    insert_timestamp = Column(BigInteger)
    user_id = Column(BigInteger)
    user_name = Column(String(100))
    shop_id = Column(BigInteger)
    shop_name = Column(String(100))
    dept_id = Column(BigInteger)
    user_ids = Column(String(100))
    user_names = Column(String(255))
    platform = Column(Integer)
    dept_ids = Column(String(500))
    dept_names = Column(String(500))
    business_manager_ids = Column(String(500))
    business_manager_names = Column(String(500))
    base_manager_ids = Column(String(500))
    base_manager_names = Column(String(500))

    # 删除历史数据
    def delete_his_data(self, platform, start_time, end_time):
        try:
            sql = text("""
                DELETE FROM his_kpi WHERE platform= :platform and insert_timestamp >= :startTime and insert_timestamp <= :endTime
            """)
            dbSession.execute(sql, {"platform": platform, "startTime": start_time, "endTime": end_time})
            dbSession.commit()
        except PendingRollbackError:
            dbSession.rollback()
        finally:
            dbSession.close()

    # 插入历史数据
    def batch_save_his_data(self, his_data):

        try:
            dbSession.bulk_save_objects(his_data)
            dbSession.flush()  # 将会话的改动发送到数据库

            # 提交到数据库
            dbSession.commit()
        except PendingRollbackError:
            dbSession.rollback()
        finally:
            # 关闭Session
            dbSession.close()


